1 'Created on August 16, 2010
2 'Tan, Angelito S.
3
4 'Date update dec 12, 2010
5 Imports System.Data.OleDb
6 Module ModCon
7 'Public fso As New filesystemobject
8 Public ParamDVFrom As New CrystalDecisions.Shared.ParameterDiscreteValue
9 Public ParamDVTo As New CrystalDecisions.Shared.ParameterDiscreteValue
10 Public ParamCompanyName As New CrystalDecisions.Shared.ParameterDiscreteValue
11 Public ParamCompanyLoc As New CrystalDecisions.Shared.ParameterDiscreteValue
12 Public ParamCompanyContact As New CrystalDecisions.Shared.ParameterDiscreteValue
13 Public ParamCompanyTIN As New CrystalDecisions.Shared.ParameterDiscreteValue
14 Public _USER As New CrystalDecisions.Shared.ParameterDiscreteValue
15 Public mReport As New CrystalDecisions.CrystalReports.Engine.ReportDocument
16 Public sqlDT As New DataTable
17 Public sqlDTx As New DataTable
18 Public openedFileStream As System.IO.Stream
19
20 'Public Const cnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=../database/SaleInv_DB.mdb"
21 'Public Const cnString As String = "Provider=SQLNCLI10;Server=CPAT;Database=SaleInv_DB; Trusted_Connection=yes;"
22 'Public Const cnString As String = "Provider=SQLNCLI10;Server=CPAT;Database=SaleInv_DB;Uid=sa; Pwd=angelito;"
23
24 'Public Const cnstring As String = "Provider=SQLOLEDB;" & _
25 ' "Data Source=;" & _
26 ' "Network=CPAT;" & _
27 ' "Initial Catalog=SaleInv_DB;" & _
28 ' "User Id=sa;" & _
29 ' "Password=angelito"
30 '192.168.1.104;" & _'
31
32 Public CnString As String
33
34 'Public conn As OleDbConnection = New OleDbConnection(cnString)
35 ' Public DataFileLock As New System.Threading.ReaderWriterLock
36
37 Public sqlSTR As String
38 Public Rpt_SqlStr As String
39 Public pass As Boolean
40 Public VAT As Double
41 Public username As String
42 Public xUser_ID As Integer
43 Public xUser_Access As String
44 Public Pending_ID As Integer
45 Public Pending_QTY As Integer
46 Public Pending_Item_ID As Integer
47 Public dataBytes() As Byte
48 Public xpass As Boolean
49 Public howx As Integer
50 Public xid(1) As Integer
51 Public xlock As Boolean
52 Public iMin As Integer
53 Public tmpStr As String
54 Public LOGID As Integer
55 Public PreviousPage, NextPage As Integer
56 Public i_Print As Integer
57
58 Public Function checkServer() As Boolean
59 Try
60
61 With FrmSERVERSETTINGS
62 .OpenFileDialog1.FileName = Application.StartupPath & "\Config.ini"
63 openedFileStream = .OpenFileDialog1.OpenFile()
64 End With
65
66 ReDim dataBytes(openedFileStream.Length - 1) 'Init
67 openedFileStream.Read(dataBytes, 0, openedFileStream.Length)
68 openedFileStream.Close()
69 tmpStr = System.Text.Encoding.Unicode.GetString(dataBytes)
70
71 With FrmSERVERSETTINGS
72 If Split(tmpStr, ":")(4) = "1" Then
73 'network
74 CnString = "Provider=SQLOLEDB;" & _
75 "Data Source=" & Split(tmpStr, ":")(0) & _
76 ";Network=" & Split(tmpStr, ":")(1) & _
77 ";Server=" & Split(tmpStr, ":")(1) & _
78 ";Initial Catalog=SaleInv_DB" & _
79 ";User Id=" & Split(tmpStr, ":")(2) & _
80 ";Password=" & Split(tmpStr, ":")(3)
81 Else
82 'local
83 'MsgBox(Split(tmpStr, ":")(1))
84 CnString = "Provider=SQLOLEDB;Server=" & Split(tmpStr, ":")(1) & _
85 ";Database=SaleInv_DB; Trusted_Connection=yes;"
86 End If
87 End With
88 Dim sqlCon As New OleDbConnection
89 sqlCon.ConnectionString = CnString
90 sqlCon.Open()
91 checkServer = True
92 sqlCon.Close()
93 Catch ex As Exception
94 checkServer = False
95 End Try
96 End Function
97
98 Public Function ExecuteSQLQuery(ByVal SQLQuery As String) As DataTable
99 Try
100 Dim sqlCon As New OleDbConnection(CnString)
101 Dim sqlDA As New OleDbDataAdapter(SQLQuery, sqlCon)
102 Dim sqlCB As New OleDbCommandBuilder(sqlDA)
103 sqlDT.Reset() ' refresh
104 sqlDA.Fill(sqlDT)
105 Catch ex As Exception
106 'MsgBox("Error: " & ex.ToString)
107 ' If Err.Number = 5 Then
108 ' MsgBox("Invalid Database, Configure TCP/IP", MsgBoxStyle.Exclamation, "Sales and Inventory")
109 ' Else
110 MsgBox("Error : " & ex.Message)
111 ' End If
112 'MsgBox("Error No. " & Err.Number & " Invalid database or no database found !! Adjust settings first", MsgBoxStyle.Critical, "Sales And Inventory")
113 'MsgBox(SQLQuery)
114 End Try
115 Return sqlDT
116 End Function
117 Public Sub FILLComboBox(ByVal sql As String, ByVal cb As ComboBox)
118 Dim conn As OleDbConnection = New OleDbConnection(CnString)
119 cb.Items.Clear()
120 Try
121 conn.Open()
122 Dim cmd As OleDbCommand = New OleDbCommand(sql, conn)
123 Dim rdr As OleDbDataReader = cmd.ExecuteReader
124 While rdr.Read
125 cb.Items.Add(rdr(0).ToString & " - " & rdr(1).ToString)
126 End While
127 rdr.Close()
128 Catch ex As Exception
129 MsgBox("Error:" & ex.ToString)
130 Finally
131 conn.Close()
132 End Try
133 End Sub
134
135 Public Sub FILLComboBox2(ByVal sql As String, ByVal cb As ComboBox)
136 Dim conn As OleDbConnection = New OleDbConnection(CnString)
137 cb.Items.Clear()
138 Try
139 conn.Open()
140 Dim cmd As OleDbCommand = New OleDbCommand(sql, conn)
141 Dim rdr As OleDbDataReader = cmd.ExecuteReader
142 While rdr.Read
143 cb.Items.Add(rdr(1).ToString)
144 End While
145 rdr.Close()
146 Catch ex As Exception
147 MsgBox("Error:" & ex.ToString)
148 Finally
149 conn.Close()
150 End Try
151 End Sub
152
153 Public Function DataSourceConnection_Report()
154 If Split(tmpStr, ":")(4) = "1" Then
155 'mReport.DataSourceConnections
156 'mReport()
157 'mReport.DataSourceConnections(0).SetConnection(Split(tmpStr, ":")(1), "SaleInv_DB", Split(tmpStr, ":")(2), Split(tmpStr, ":")(3))
158 mReport.DataSourceConnections(0).SetConnection(Split(tmpStr, ":")(1), "SaleInv_DB", False)
159 'MsgBox(Split(tmpStr, ":")(2) & " " & Split(tmpStr, ":")(3))
160 mReport.DataSourceConnections(0).SetLogon(Split(tmpStr, ":")(2), Split(tmpStr, ":")(3))
161 Else
162
163 mReport.DataSourceConnections(0).SetConnection(Split(tmpStr, ":")(1), "SaleInv_DB", True)
164 End If
165 'MsgBox(mReport.DataSourceConnections(0).ServerName.ToString)
166 Return 0
167 End Function
168 End Module
